package leaveSystem.dao.mysql;

import leaveSystem.dao.DAOException;
import leaveSystem.dao.RoleDAO;
import leaveSystem.model.Role;

import java.sql.*;
import java.sql.Date;
import java.util.*;
import java.math.*;

public class RoleDAOImpl implements RoleDAO {

	    public Role createValueObject() {
	          return new Role();
	    }

	 /*   public Role getObject(Connection conn, int roleId) throws DAOException, SQLException {

	          Role valueObject = createValueObject();
	          valueObject.setRoleId(roleId);
	          load(conn, valueObject);
	          return valueObject;
	    }

	    public void load(Connection conn, Role valueObject) throws DAOException, SQLException {

	          String sql = "SELECT * FROM role WHERE (roleid = ? ) "; 
	          PreparedStatement stmt = null;

	          try {
	               stmt = conn.prepareStatement(sql);
	               stmt.setInt(1, valueObject.getRoleId()); 

	               singleQuery(conn, stmt, valueObject);

	          } finally {
	              if (stmt != null)
	                  stmt.close();
	          }
	    }*/

	    public List<Role> loadAll(Connection conn) throws SQLException {

	          String sql = "SELECT * FROM leaveapp.role ORDER BY roleid ASC ";
	          List<Role> searchResults = listQuery(conn, conn.prepareStatement(sql));

	          return searchResults;
	    }
	    
	    public void insertRole(Connection conn, Role valueObject) throws SQLException {

	          String sql = "";
	          PreparedStatement stmt = null;
	          ResultSet result = null;

	          try {
	               sql = "INSERT INTO leaveapp.role ( roleid, role_name, description, "+ "createdBy, createdTimeStamp) VALUES (?, ?, ?, ?, ?) ";
	               stmt = conn.prepareStatement(sql);

	               stmt.setInt(1, valueObject.getRoleId()); 
	               stmt.setString(2, valueObject.getRoleName()); 
	               stmt.setString(3, valueObject.getDescription()); 
	               stmt.setString(4, valueObject.getCreatedBy()); 
	               stmt.setDate(5, (Date) valueObject.getCreatedTimeStamp()); 

	               int rowcount = databaseUpdate(conn, stmt);
	               if (rowcount != 1) {
	                    //System.out.println("PrimaryKey Error when updating DB!");
	                    throw new SQLException("PrimaryKey Error when updating DB!");
	               }

	          } finally {
	              if (stmt != null)
	                  stmt.close();
	          }


	    }

	    public void updateRole(Connection conn, Role valueObject) throws DAOException, SQLException {

	          String sql = "UPDATE leaveapp.role SET role_name = ?, description = ?, createdBy = ?, "
	               + "createdTimeStamp = ? WHERE (roleid = ? ) ";
	          PreparedStatement stmt = null;

	          try {
	              stmt = conn.prepareStatement(sql);
	              stmt.setString(1, valueObject.getRoleName()); 
	              stmt.setString(2, valueObject.getDescription()); 
	              stmt.setString(3, valueObject.getCreatedBy()); 
	              stmt.setDate(4, (Date) valueObject.getCreatedTimeStamp()); 

	              stmt.setInt(5, valueObject.getRoleId()); 

	              int rowcount = databaseUpdate(conn, stmt);
	              if (rowcount == 0) {
	                   //System.out.println("Object could not be saved! (PrimaryKey not found)");
	                   throw new DAOException("Object could not be saved! (PrimaryKey not found)");
	              }
	              if (rowcount > 1) {
	                   //System.out.println("PrimaryKey Error when updating DB! (Many objects were affected!)");
	                   throw new SQLException("PrimaryKey Error when updating DB! (Many objects were affected!)");
	              }
	          } finally {
	              if (stmt != null)
	                  stmt.close();
	          }
	    }

	    public void deleteRole(Connection conn, Role valueObject) throws DAOException, SQLException {

	          String sql = "DELETE FROM leaveapp.role WHERE (roleid = ? ) ";
	          PreparedStatement stmt = null;

	          try {
	              stmt = conn.prepareStatement(sql);
	              stmt.setInt(1, valueObject.getRoleId()); 

	              int rowcount = databaseUpdate(conn, stmt);
	              if (rowcount == 0) {
	                   //System.out.println("Object could not be deleted (PrimaryKey not found)");
	                   throw new DAOException("Object could not be deleted! (PrimaryKey not found)");
	              }
	              if (rowcount > 1) {
	                   //System.out.println("PrimaryKey Error when updating DB! (Many objects were deleted!)");
	                   throw new SQLException("PrimaryKey Error when updating DB! (Many objects were deleted!)");
	              }
	          } finally {
	              if (stmt != null)
	                  stmt.close();
	          }
	    }

	    public void deleteAllRole(Connection conn) throws SQLException {

	          String sql = "DELETE FROM leaveapp.role";
	          PreparedStatement stmt = null;

	          try {
	              stmt = conn.prepareStatement(sql);
	              int rowcount = databaseUpdate(conn, stmt);
	          } finally {
	              if (stmt != null)
	                  stmt.close();
	          }
	    }

	    public int countAll(Connection conn) throws SQLException {

	          String sql = "SELECT count(*) FROM leaveapp.role";
	          PreparedStatement stmt = null;
	          ResultSet result = null;
	          int allRows = 0;

	          try {
	              stmt = conn.prepareStatement(sql);
	              result = stmt.executeQuery();

	              if (result.next())
	                  allRows = result.getInt(1);
	          } finally {
	              if (result != null)
	                  result.close();
	              if (stmt != null)
	                  stmt.close();
	          }
	          return allRows;
	    }

	    public List<Role> findRole(Connection conn, Role valueObject) throws SQLException {

	          List searchResults;

	          boolean first = true;
	          StringBuffer sql = new StringBuffer("SELECT * FROM leaveapp.role WHERE 1=1 ");

	          if (valueObject.getRoleId() != 0) {
	              if (first) { first = false; }
	              sql.append("AND roleid = ").append(valueObject.getRoleId()).append(" ");
	          }

	          if (valueObject.getRoleName() != null) {
	              if (first) { first = false; }
	              sql.append("AND role_name LIKE '").append(valueObject.getRoleName()).append("%' ");
	          }

	          if (valueObject.getDescription() != null) {
	              if (first) { first = false; }
	              sql.append("AND description LIKE '").append(valueObject.getDescription()).append("%' ");
	          }

	          if (valueObject.getCreatedBy() != null) {
	              if (first) { first = false; }
	              sql.append("AND createdBy LIKE '").append(valueObject.getCreatedBy()).append("%' ");
	          }

	          if (valueObject.getCreatedTimeStamp() != null) {
	              if (first) { first = false; }
	              sql.append("AND createdTimeStamp = '").append(valueObject.getCreatedTimeStamp()).append("' ");
	          }


	          sql.append("ORDER BY roleid ASC ");

	          // Prevent accidential full table results.
	          // Use loadAll if all rows must be returned.
	          if (first)
	               searchResults = new ArrayList();
	          else
	               searchResults = listQuery(conn, conn.prepareStatement(sql.toString()));

	          return searchResults;
	    }

	    public int databaseUpdate(Connection conn, PreparedStatement stmt) throws SQLException {

	          int result = stmt.executeUpdate();

	          return result;
	    }

	    /*protected void singleQuery(Connection conn, PreparedStatement stmt, Role valueObject) 
	          throws DAOException, SQLException {

	          ResultSet result = null;

	          try {
	              result = stmt.executeQuery();

	              if (result.next()) {

	                   valueObject.setRoleId(result.getInt("roleid")); 
	                   valueObject.setRoleName(result.getString("role_name")); 
	                   valueObject.setDescription(result.getString("description")); 
	                   valueObject.setCreatedBy(result.getString("createdBy")); 
	                   valueObject.setCreatedTimeStamp(result.getTimestamp("createdTimeStamp")); 

	              } else {
	                    //System.out.println("Role Object Not Found!");
	                    throw new DAOException("Role Object Not Found!");
	              }
	          } finally {
	              if (result != null)
	                  result.close();
	              if (stmt != null)
	                  stmt.close();
	          }
	    }*/

	    public List<Role> listQuery(Connection conn, PreparedStatement stmt) throws SQLException {

	          ArrayList<Role> searchResults = new ArrayList<Role>();
	          ResultSet result = null;

	          try {
	              result = stmt.executeQuery();

	              while (result.next()) {
	                   Role temp = createValueObject();

	                   temp.setRoleId(result.getInt("roleid")); 
	                   temp.setRoleName(result.getString("role_name")); 
	                   temp.setDescription(result.getString("description")); 
	                   temp.setCreatedBy(result.getString("createdBy")); 
	                   temp.setCreatedTimeStamp(result.getTimestamp("createdTimeStamp")); 

	                   searchResults.add(temp);
	              }

	          } finally {
	              if (result != null)
	                  result.close();
	              if (stmt != null)
	                  stmt.close();
	          }

	          return (List)searchResults;
	    }
	   
}
